﻿using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using Web;
using System.Text;
using SHJunketingBLL;

public partial class Admin_UserManagement_QueryReport : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            //绑定数据

            LoadJigou();

            DataView ldvlist = new DataView();
            ldvlist.Table = GetUser();

            if (ldvlist.Table != null)
            {
                AspNetPager1.RecordCount = ldvlist.Table.Rows.Count;
            }
            else 
            {
                AspNetPager1.RecordCount = 0;
            }

            Session["dvlist"] = ldvlist;

            bindData();
        }
    }

    #region 数据绑定
    /// <summary>
    /// 数据绑定
    /// </summary>    
    void bindData()
    {
        PagedDataSource pds = new PagedDataSource();
        pds.AllowPaging = true;
        pds.PageSize = AspNetPager1.PageSize;
        pds.CurrentPageIndex = AspNetPager1.CurrentPageIndex - 1;
        pds.DataSource = (DataView)Session["dvlist"];
        Repeater1.DataSource = pds;
        Repeater1.DataBind();
    }
    #endregion

    //加载医疗机构
    private DataTable GetAllJigou()
    {
        DataTable dt = HospitalBll.GetAllJigou();
        return dt;
    }

    public DataTable dt = new DataTable();
    protected void LoadJigou()
    {
        dt = GetAllJigou();
        this.ddljigou.Items.Clear();      //清除DropDownList中的内容

        this.ddljigou.Items.Add(new ListItem("全部", ""));
        for (int i = 0; i < dt.Rows.Count; i++)
        {
            this.ddljigou.Items.Add(new ListItem(dt.Rows[i]["name"].ToString(), dt.Rows[i]["id"].ToString()));
        }
        
    }

    protected void btnsearch_Click(object sender, EventArgs e)
    {
        string sql = "";
        string agencyName = this.ddljigou.SelectedValue;//机构名称



        string time = this.DropDownList2.SelectedValue;      //起始时间
        string time1 = this.DropDownList3.SelectedValue;     //结束

        if (this.RadioButton1.Checked == true)
        {
            sql = "select b.userid,*,'BookingStatus'= case when BookingStatus =0 then '已预约体检' when BookingStatus =1 then '已取消体检' when BookingStatus =2 then '已经体检' end from Users b inner join MedicalAppointment c on b.UserID = c.UserID WHERE (DATEDIFF(d,c.ReservationTime,GETDATE())=0)";
        }
        if (this.RadioButton2.Checked == true)
        {
            sql = "select b.userid,*,'BookingStatus'= case when BookingStatus =0 then '已预约体检' when BookingStatus =1 then '已取消体检' when BookingStatus =2 then '已经体检' end from Users b inner join MedicalAppointment c on b.UserID = c.UserID where c.ReservationTime between '" + time + "' and '" + time1;
        }
        if (agencyName != "") 
        {
            sql += " and c.AgencyName='" + agencyName + "'";
        }


        sql += "  ORDER BY c.ReservationTime DESC,b.UserLogin DESC";

        DataView ldvlist = new DataView();
        ldvlist.Table = DBHelper.GetDataSet(sql);

        if (ldvlist.Table == null)
        { AspNetPager1.RecordCount = 0; }
        else
        {
            AspNetPager1.RecordCount = ldvlist.Table.Rows.Count;
        }

        Session["dvlist"] = ldvlist;
        bindData();
    }
    //打印报表
    protected void Button1_Click(object sender, EventArgs e)
    {

        DataView ldvlist = (DataView)Session["dvlist"];

        meDataGirdToExcel(ldvlist.Table);

    }


    public static void meDataGirdToExcel(DataTable dt)
    {
        System.Web.HttpResponse resp = System.Web.HttpContext.Current.Response; ;

        if (dt.Rows.Count == 0)
        {
            resp.Write(" <script>alert('对不起，没有查询到任何记录，无法导出') </script>");
            resp.End();
        }

        resp.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
        resp.ContentType = "application/ms-excel";
        resp.AppendHeader("Content-Disposition", "attachment;filename=" + DateTime.Now.ToString() + ".xls");

        int colCount = dt.Columns.Count;
        System.Text.StringBuilder sb = new StringBuilder();
        sb.Append(" <html> <head>\n");
        sb.Append(" <meta http-equiv=\"Content-Language\" content=\"zh-cn\">\n");
        sb.Append(" <meta http-equiv=\"Content-Type\" content=\"text/html; charset=gb2312\">\n");
        sb.Append(" </head>\n");
        sb.Append(" <table border=1>");


        //查询时间 
        sb.Append(" <tr> <td colspan=" + colCount + " valign=middle height=24>");
        sb.Append("导出时间：" + System.DateTime.Now.ToString() + " </td> </tr>");
        //表标题 
        sb.Append(" <tr>\n");
        for (int i = 0; i < colCount; i++)
        {
            string sCaption = dt.Columns[i].Caption.ToString();
            if (sCaption.ToLower() != "id")
                sb.Append(" <td bgcolor=#969696> <b>" + dt.Columns[i].Caption.ToString() + " </b> </td>\n");
        }
        //表内容 
        foreach (DataRow dr in dt.Rows)
        {
            sb.Append(" <tr>");
            for (int j = 0; j < colCount; j++)
            {
                if (dt.Columns[j].Caption.ToString().ToLower() != "id")
                    sb.Append(" <td style='vnd.ms-excel.numberformat:@'>" + dr[j].ToString() + " </td>");
            }
            sb.Append(" </tr>\n");
        }
        sb.Append(" </table>\n");

        resp.Write(sb.ToString());
        resp.End();
    }

   


    protected void AspNetPager1_PageChanged(object sender, EventArgs e)
    {
        bindData();
    }

    public System.Data.DataTable GetUser() 
    {
        string time = this.DropDownList2.SelectedValue;      //起始时间
        string time1 = this.DropDownList3.SelectedValue;     //结束
        string sql = "";
        if (this.RadioButton1.Checked == true)
        {
            sql = "select b.userid,*,'BookingStatus'= case when BookingStatus =0 then '已预约体检' when BookingStatus =1 then '已取消体检' when BookingStatus =2 then '已经体检' end from Users b inner join MedicalAppointment c on b.UserID = c.UserID WHERE (DATEDIFF(d,c.ReservationTime,GETDATE())=0) ORDER BY c.ReservationTime DESC,b.UserLogin DESC";
        }
        if (this.RadioButton2.Checked == true)
        {
            sql = "select b.userid,*,'BookingStatus'= case when BookingStatus =0 then '已预约体检' when BookingStatus =1 then '已取消体检' when BookingStatus =2 then '已经体检' end from Users b inner join MedicalAppointment c on b.UserID = c.UserID where c.ReservationTime between '" + time + "' and '" + time1 + "' ORDER BY c.ReservationTime DESC,b.UserLogin DESC";
        }
        //string sql = "select b.userid,*,'BookingStatus'= case when BookingStatus =0 then '已预约体检' when BookingStatus =1 then '已取消体检' when BookingStatus =2 then '已经体检' end from Users b inner join MedicalAppointment c on b.UserID = c.UserID  where c.ReservationTime between '" + time + "' and '" + time1 + "' ORDER BY c.ReservationTime,b.UserLogin DESC";
        System.Data.DataTable table = DBHelper.GetDataSet(sql);
        if (table.Rows.Count > 0) 
        {
            return table;
        }
        return null;
    }

    //查找用户体检状态
    public string getAgency(string userId)
    {
        string sql1 = "select * from MedicalAppointment where UserID ='" + userId + "'";
        System.Data.DataTable table1 = DBHelper.GetDataSet(sql1);
        if (table1.Rows.Count > 0) //有预约体检
        {
            string BookingStatus = table1.Rows[0]["BookingStatus"].ToString();
            if (BookingStatus.Equals("0"))
            {
                return "已预约";
            }
            else if (BookingStatus.Equals("1"))
            {
                return "已取消";
            }
            else
            {
                return "已体检";
            }
        }
        else
        {
            return "未预约";
        }
    }
    protected void RadioButton2_CheckedChanged(object sender, EventArgs e)
    {
        this.DropDownList2.Enabled = true;
        this.DropDownList3.Enabled = true;
    }
    protected void RadioButton1_CheckedChanged(object sender, EventArgs e)
    {
        this.DropDownList2.Enabled = false;
        this.DropDownList3.Enabled = false;
    }

    protected void ddljigou_SelectedIndexChanged(object sender, EventArgs e)
    {
        string sql = "";
        string agencyName = this.ddljigou.SelectedValue;//机构名称

        try
        {

            string time = this.DropDownList2.SelectedValue;      //起始时间
            string time1 = this.DropDownList3.SelectedValue;     //结束

            if (this.RadioButton1.Checked == true)
            {
                sql = "select b.userid,*,'BookingStatus'= case when BookingStatus =0 then '已预约体检' when BookingStatus =1 then '已取消体检' when BookingStatus =2 then '已经体检' end from Users b inner join MedicalAppointment c on b.UserID = c.UserID WHERE (DATEDIFF(d,c.ReservationTime,GETDATE())=0) ORDER BY c.ReservationTime DESC,b.UserLogin DESC";
            }
            if (this.RadioButton2.Checked == true)
            {
                sql = "select b.userid,*,'BookingStatus'= case when BookingStatus =0 then '已预约体检' when BookingStatus =1 then '已取消体检' when BookingStatus =2 then '已经体检' end from Users b inner join MedicalAppointment c on b.UserID = c.UserID where c.ReservationTime between '" + time + "' and '" + time1 + "' ORDER BY c.ReservationTime DESC,b.UserLogin DESC";
            }
            if (agencyName != " ")
            {
                sql += " and AgencyName='" + agencyName + "'";
            }

            DataView ldvlist = new DataView();
            ldvlist.Table = DBHelper.GetDataSet(sql);
            AspNetPager1.RecordCount = ldvlist.Table.Rows.Count;
            Session["dvlist"] = ldvlist;
            bindData();
        }
        catch { }
    }
}
